﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Web;
using System.Web.Http;
using bmapi.HelperClass;
using bmapi.Models.DataEntity;
using static bmapi.Models.ShowModel;

namespace bmapi.Controllers
{
    public class StatisticsController : PageController
    {
        /// <summary>
        /// 往年统计图
        /// </summary>
        /// <param name="year">年份 要求格式</param>
        /// <returns></returns>
        [HttpGet, Route("PastYearStatistics")]
        public HttpResponseMessage FormerYearMap(string year, int PLID)
        {
            string sql = "";
            //查询出一年当中样板数，生产单数，件数，床数，铺布总长度,特体Pod数的节点
            if (SQLCommand.IsOracle)
                sql = "select to_char(day,'mm') as newtime,model_count,order_count,quantity,bed,len,pod from Stats_tb where to_char(day,'yyyy')=" + year + " and PLID=" + PLID + "";
            else
                sql = "select datepart(mm,day) as newtime,model_count,order_count,quantity,bed,len,pod from Stats_tb where DATEPART(YEAR,day)=" + year + " and PLID=" + PLID + "";
            SQLCommand sc = new SQLCommand(sql);
            DataTable dt = sc.ExecuteScalars();
            //定义存储对象的数组
            if (dt != null)
            {
                for (int i = 1; i <= dt.Rows.Count; i++)
                {
                    DataRow row = dt.NewRow();
                    if (Convert.ToInt32(dt.Rows[i - 1]["newtime"]) != i)
                    {
                        row["newtime"] = i;
                        row["model_count"] = 0;
                        row["order_count"] = 0;
                        row["quantity"] = 0;
                        row["quantity"] = 0;
                        row["bed"] = 0;
                        row["len"] = 0;
                        row["pod"] = 0;
                        dt.Rows.InsertAt(row, i - 1);
                    }
                    else
                    {
                        continue;
                    }
                }
                List<double> modelcount = new List<double>();
                List<double> ordercount = new List<double>();
                List<double> numbercount = new List<double>();
                List<double> bedcount = new List<double>();
                List<double> lencount = new List<double>();
                List<double> podcount = new List<double>();
                foreach (DataRow dr in dt.Rows)
                {
                    modelcount.Add(Convert.ToDouble(dr["model_count"]));
                    ordercount.Add(Convert.ToDouble(dr["order_count"]));
                    numbercount.Add(Convert.ToDouble(dr["quantity"]));
                    bedcount.Add(Convert.ToDouble(dr["bed"]));
                    lencount.Add(Convert.ToDouble(dr["len"]));
                    podcount.Add(Convert.ToDouble(dr["pod"]));
                }
                return AssistFun.toJson(Choose(modelcount, ordercount, numbercount, bedcount, lencount, podcount));
            }
            else
            {
                return AssistFun.toJson(new RespondModel(201, "无数据"));
            }
        }

        /// <summary>
        /// 往年统计表
        /// </summary>
        /// <param name="year">年份 </param>
        /// <returns></returns>
        [HttpGet, Route("PastYearStatisticsTable")]
        public HttpResponseMessage FormerYearTable(string year, int PLID)
        {
            string sql = "";
            //查询出一年当中样板数，生产单数，件数，床数，铺布总长度,特体Pod数的总数和平均数
            if (SQLCommand.IsOracle)
                sql = "select SUM(model_count) as SumModel,AVG(model_count) as AvgModel,SUM(order_count) as SumOrder,AVG(order_count) as AvgOrder,SUM(quantity) as SumNumber,AVG(quantity) as AvgNumber,SUM(bed) as SumBed,AVG(bed) as AvgBed,SUM(len) as SumLen,AVG(len) as AvgLen,SUM(pod) as SumPod,AVG(pod) as AvgPod from Stats_tb  where to_char(day,'yyyy')=" + year + " and PLID=" + PLID + "";
            else
                sql = "select SUM(model_count) as SumModel,AVG(model_count) as AvgModel,SUM(order_count) as SumOrder,AVG(order_count) as AvgOrder,SUM(quantity) as SumNumber,AVG(quantity) as AvgNumber,SUM(bed) as SumBed,AVG(bed) as AvgBed,SUM(len) as SumLen,AVG(len) as AvgLen,SUM(pod) as SumPod,AVG(pod) as AvgPod from Stats_tb  where DATEPART(YEAR,day)=" + year + " and PLID=" + PLID + "";
            SQLCommand sc = new SQLCommand(sql);
            DataTable dt = sc.ExecuteScalars();
            if (dt == null)
                return AssistFun.toJson(new RespondModel(201, "无数据记录"));
            List<Number> numbers = AssistFun.DataTableToList<Number>(dt);
            return AssistFun.toJson(new RespondModel(200, "成功", new data(1, numbers.ConvertAll(c => (object)c))));


        }

        /// <summary>
        ///本年统计图
        /// </summary>
        ///<returns></returns>
        [HttpGet, Route("CurrentYearStatistics")]
        public HttpResponseMessage ThatYearMap(int PLID)
        {
            string sql = "";
            StringBuilder sb = new StringBuilder();
            SQLCommand sc = null;
            DataTable model = null;
            DataTable ordernumber = null;
            DataTable bedlen = null;
            DataTable pod = null;
            if (SQLCommand.IsOracle)
            {
                //查询本年样板数统计节点
                sb.AppendFormat("select newTime, nvl(count(num),0) as model from (select a.calendar_day,nvl(time,null) as num from (select calendar_day from (select to_date((select to_char(sysdate , 'yyyy') from dual) || '0101',  'yyyymmdd') + level-1  as calendar_day from dual connect by level <to_char(to_date((select to_char(sysdate, 'yyyy') from dual) || '1231', 'yyyymmdd'), 'ddd'))");
                sb.AppendFormat("where calendar_day>= trunc(sysdate)-366 and calendar_day<trunc(sysdate)+1) a left join (select time from model_tb where to_char(sysdate,'yyyy')=to_char(time,'yyyy') and PLID=" + PLID + ") b on a.calendar_day=b.time order by a.calendar_day) c, (select sysdate - (level-1) * 14 newTime from dual connect by level <= 26) d where c.calendar_day<newTime and c.calendar_day> d.newTime-14 group by d.newTime order by d.newTime");
                sc = new SQLCommand(sb.ToString());
                model = sc.ExecuteScalars();
                //查询本年生产单数,件数统计节点
                sb.Clear();
                sb.AppendFormat("select newTime, nvl(count(num),0) as orders,nvl(sum(num),0) as quantity from (select a.calendar_day,nvl(quantity,null) as num from (select calendar_day from (select to_date((select to_char(sysdate , 'yyyy') from dual) || '0101',  'yyyymmdd') + level-1  as calendar_day from dual connect by level <to_char(to_date((select to_char(sysdate, 'yyyy') from dual) || '1231', 'yyyymmdd'), 'ddd'))");
                sb.AppendFormat("where calendar_day>= trunc(sysdate)-366 and calendar_day<trunc(sysdate)+1) a left join (select creater_time,quantity from order_tb where to_char(sysdate,'yyyy')=to_char(creater_time,'yyyy') and state!=0 and o_PLID=" + PLID + ") b on a.calendar_day=b.creater_time order by a.calendar_day) c, (select sysdate - (level-1) * 14 newTime from dual connect by level <= 26) d where c.calendar_day<newTime and c.calendar_day> d.newTime-14 group by d.newTime order by d.newTime");
                sc = new SQLCommand(sb.ToString());
                ordernumber = sc.ExecuteScalars();
                //查询本年床数,铺布总长度统计节点
                sb.Clear();
                sb.AppendFormat("select newTime, nvl(count(num),0) as bed,nvl(sum(num),0) as len from (select a.calendar_day,nvl(lend,null) as num from (select calendar_day from (select to_date((select to_char(sysdate , 'yyyy') from dual) || '0101',  'yyyymmdd') + level-1  as calendar_day from dual connect by level <to_char(to_date((select to_char(sysdate, 'yyyy') from dual) || '1231', 'yyyymmdd'), 'ddd'))");
                sb.AppendFormat("where calendar_day>= trunc(sysdate)-366 and calendar_day<trunc(sysdate)+1) a left join (select cut_time,round(lay_count*lay_len,2) as lend from bed_tb where to_char(sysdate,'yyyy')=to_char(cut_time,'yyyy') and PLID=" + PLID + ") b on a.calendar_day=b.cut_time order by a.calendar_day) c, (select sysdate - (level-1) * 14 newTime from dual connect by level <= 26) d where c.calendar_day<newTime and c.calendar_day> d.newTime-14 group by d.newTime order by d.newTime");
                sc = new SQLCommand(sb.ToString());
                bedlen = sc.ExecuteScalars();
                //查询本年特定Pod数统计节点
                sb.Clear();
                sb.AppendFormat("select newTime, nvl(count(num),0) as pod from (select a.calendar_day,nvl(time,null) as num from (select calendar_day from (select to_date((select to_char(sysdate , 'yyyy') from dual) || '0101',  'yyyymmdd') + level-1  as calendar_day from dual connect by level <to_char(to_date((select to_char(sysdate, 'yyyy') from dual) || '1231', 'yyyymmdd'), 'ddd'))");
                sb.AppendFormat("where calendar_day>= trunc(sysdate)-366 and calendar_day<trunc(sysdate)+1) a left join (select time from pod_tb  left join order_tb on pod_tb.order_id=order_tb.id and to_char(sysdate,'yyyy')=to_char(time,'yyyy') and o_PLID=" + PLID + ") b on a.calendar_day=b.time order by a.calendar_day) c, (select sysdate - (level-1) * 14 newTime from dual connect by level <= 26) d where c.calendar_day<newTime and c.calendar_day> d.newTime-14 group by d.newTime order by d.newTime");
                sc = new SQLCommand(sb.ToString());
                pod = sc.ExecuteScalars();
            }
            else
            {
                //查询本年样板数统计节点
                sql = "SELECT A.newTime,SUM(A.model) AS model FROM (select DateDiff(DAY,time,GETDATE())/14 as newTime,COUNT(*) as model from Model_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,time) and PLID=" + PLID + " group by DateDiff(DAY,time,GETDATE())/14 " + Model();
                sc = new SQLCommand(sql);
                model = sc.ExecuteScalars();
                //查询本年生产单数,件数统计节点
                sql = "SELECT A.newTime,SUM(A.orders) AS orders,SUM(A.number) AS quantity FROM (SELECT DateDiff(DAY, creater_time, GETDATE()) / 14 AS newTime, COUNT(*) as orders, SUM(quantity) as number from Order_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,creater_time) and state!=0 and o_PLID=" + PLID + " group by DateDiff(DAY, creater_time, GETDATE()) / 14" + OrderNumber();
                sc = new SQLCommand(sql);
                ordernumber = sc.ExecuteScalars();
                //查询本年床数,铺布总长度统计节点
                sql = "SELECT A.newTime,SUM(A.bed) AS bed,SUM(A.len) AS len FROM (select DateDiff(DAY,cut_time,GETDATE())/14 as newTime,COUNT(*) as bed,Round(SUM(lay_count*lay_len),2) as len from Bed_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,cut_time) and PLID=" + PLID + " group by DateDiff(DAY,cut_time,GETDATE())/14 " + BedLen();
                sc = new SQLCommand(sql);
                bedlen = sc.ExecuteScalars();
                //查询本年特定Pod数统计节点
                sql = "SELECT A.newTime,SUM(A.pod) AS pod FROM (select DateDiff(DAY,time,GETDATE())/14 as newTime,COUNT(*) as pod from Pod_tb left join order_tb on pod_tb.order_id=order_tb.id and DATEPART(YYYY,GETDATE())=DATEPART(YYYY,time) and o_PLID=" + PLID + " group by DateDiff(DAY,time,GETDATE())/14 " + Pod();
                sc = new SQLCommand(sql);
                pod = sc.ExecuteScalars();
            }
            if (model == null && bedlen == null && ordernumber == null && pod == null)
            {
                return AssistFun.toJson("无数据");
            }
            else
            {
                //定义存储对象的数组
                List<int> newTime = new List<int>();
                List<double> modelcount = new List<double>();
                List<double> ordercount = new List<double>();
                List<double> numbercount = new List<double>();
                List<double> bedcount = new List<double>();
                List<double> lencount = new List<double>();
                List<double> podcount = new List<double>();
                int weeks = Convert.ToInt32(DateTime.Now.DayOfYear.ToString()) % 14 == 0 ? Convert.ToInt32(DateTime.Now.DayOfYear.ToString()) / 14 : Convert.ToInt32(DateTime.Now.DayOfYear.ToString()) / 14 + 1;
                List<int> week = new List<int>();
                List<int> diff = new List<int>();
                if (newTime.Count != weeks)
                {
                    for (int i = 0; i < weeks; i++)
                    {
                        week.Add(i + 1);
                    }
                    diff = week.Where(c => !newTime.Contains(c)).ToList();
                }

                foreach (DataRow ml in model.Rows)
                {
                    modelcount.Add(Convert.ToDouble(ml["model"]));
                }
                foreach (DataRow or in ordernumber.Rows)
                {
                    ordercount.Add(Convert.ToDouble(or["orders"]));
                    numbercount.Add(Convert.ToDouble(or["quantity"]));
                }
                foreach (DataRow bn in bedlen.Rows)
                {
                    bedcount.Add(Convert.ToDouble(bn["bed"]));
                    lencount.Add(Convert.ToDouble(bn["len"]));
                }
                foreach (DataRow pd in pod.Rows)
                {
                    podcount.Add(Convert.ToDouble(pd["pod"]));
                }
                return AssistFun.toJson(Choose(modelcount, ordercount, numbercount, bedcount, lencount, podcount));
            }
        }
        /// <summary>
        /// 本年统计表
        /// </summary> 
        /// <returns></returns>
        [HttpGet, Route("CurrentYearTable")]
        public HttpResponseMessage ThatYearTable(int PLID)
        {
            string sql = "";  
            //string sql = "select COUNT(*) as SumModel,COUNT(*)/COUNT(DateDiff(DAY,Model_tb.time,GETDATE())/14) as AvgModel,COUNT(*) as SumOrder,COUNT(*)/COUNT(DateDiff(DAY,creater_time,GETDATE())/14) as AvgOrder,SUM(number) as SumNumber,SUM(number)/COUNT(DateDiff(DAY,creater_time,GETDATE())/14) as AvgNumber,COUNT(*) as SumBed,COUNT(*)/COUNT(DateDiff(DAY,lay_time,GETDATE())/14) as AvgBed,SUM(lay_count*lay_len) as SumLen,SUM(lay_count*lay_len)/COUNT(DateDiff(DAY,lay_time,GETDATE())/14) as AvgLen,COUNT(*) as SumPod,COUNT(*)/COUNT(DateDiff(DAY,Pod_tb.time,GETDATE())/14) as AvgPod from Model_tb,Order_tb,Bed_tb,Pod_tb where Model_tb.PLID=Order_tb.o_PLID and Order_tb.o_PLID=Bed_tb.PLID and Order_tb.id=Pod_tb.order_id and DATEPART(YYYY,GETDATE())=DATEPART(YYYY,Model_tb.time) and DATEPART(YYYY,GETDATE())=DATEPART(YYYY,creater_time) and DATEPART(YYYY,GETDATE())=DATEPART(YYYY,lay_time) and DATEPART(YYYY,GETDATE())=DATEPART(YYYY,Pod_tb.time)";
            //查询样板数总和及平均样板数
            DataTable model = new DataTable();
            SQLCommand sc = null;
            try
            {
                if (SQLCommand.IsOracle)
                    sql = "select COUNT(*) as SumModel,Round(count(*)/(ceil(sysdate-trunc(sysdate,'yyyy'))/14),2) as AvgModel from Model_tb where to_char(sysdate,'yyyy')=to_char(time,'yyyy') and PLID=" + PLID + "";
                else
                    sql = "select COUNT(*) as SumModel,Round(COUNT(*)/CEILING(CONVERT(float,datepart(dayofyear,getDate()))/14),2) as AvgModel from Model_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,time) and PLID=" + PLID + "";
                sc = new SQLCommand(sql);
                model = sc.ExecuteScalars();
            }
            catch (Exception)
            {
                model.Columns.Add(new DataColumn("SumModel"));
                model.Columns.Add(new DataColumn("AvgModel"));
                DataRow row = model.NewRow();
                row["SumModel"] = 0;
                row["AvgModel"] = 0;
                model.Rows.Add(row);
            }

            //查询生产单数总和，平均生产单数，件数总和，平均件数（产量）
            DataTable ordernumber = new DataTable();
            try
            {
                if(SQLCommand.IsOracle)
                    sql = "select COUNT(*) as SumOrder,Round(count(*)/(ceil(sysdate-trunc(sysdate,'yyyy'))/14),2) as AvgOrder,SUM(quantity) as SumNumber,Round(SUM(quantity)/(ceil(sysdate-trunc(sysdate,'yyyy'))/14),2) as AvgNumber from Order_tb where to_char(sysdate,'yyyy')=to_char(creater_time,'yyyy') and state!=0 and o_PLID=" + PLID+"";
                else
                    sql = "select COUNT(*) as SumOrder,Round(COUNT(*)/CEILING(CONVERT(float,datepart(dayofyear,getDate()))/14),2) as AvgOrder,isnull(sum(isnull(quantity,0)),0) as SumNumber,Round(isnull(sum(isnull(quantity,0)),0)/CEILING(CONVERT(float,datepart(dayofyear,getDate()))/14),2) as AvgNumber from Order_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,creater_time) and state!=0 and o_PLID=" + PLID + "";
                sc = new SQLCommand(sql);
                ordernumber = sc.ExecuteScalars();
            }
            catch (Exception)
            {
                ordernumber.Columns.Add(new DataColumn("SumOrder"));
                ordernumber.Columns.Add(new DataColumn("AvgOrder"));
                ordernumber.Columns.Add(new DataColumn("SumNumber"));
                ordernumber.Columns.Add(new DataColumn("AvgNumber"));
                DataRow row = ordernumber.NewRow();
                row["SumOrder"] = "0";
                row["AvgOrder"] = "0";
                row["SumNumber"] = "0";
                row["AvgNumber"] = "0";
                ordernumber.Rows.Add(row);
            }

            //查询床数总和，平均床数，铺布总长度总和，平均铺布总长度 
            DataTable bedlen = new DataTable();
            try
            {
                if (SQLCommand.IsOracle)
                    sql = "select COUNT(*) as SumBed,Round(count(*)/(ceil(sysdate-trunc(sysdate,'yyyy'))/14),2) as AvgBed,Round(SUM(lay_count*lay_len),2) as SumLen,Round(SUM(lay_count*lay_len)/(ceil(sysdate-trunc(sysdate,'yyyy'))/14),2) as AvgLen from Bed_tb where to_char(sysdate,'yyyy')=to_char(cut_time,'yyyy') and PLID=" + PLID + "";
                else
                    sql = "select COUNT(*) as SumBed,Round(COUNT(*)/CEILING(CONVERT(float,datepart(dayofyear,getDate()))/14),2) as AvgBed,Round(SUM(lay_count*lay_len),2) as SumLen,Round(SUM(lay_count*lay_len)/CEILING(CONVERT(float,datepart(dayofyear,getDate()))/14),2) as AvgLen from Bed_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,cut_time) and PLID=" + PLID + "";
                sc = new SQLCommand(sql);
                bedlen = sc.ExecuteScalars();
            }
            catch (Exception)
            {
                bedlen.Columns.Add(new DataColumn("SumBed"));
                bedlen.Columns.Add(new DataColumn("AvgBed"));
                bedlen.Columns.Add(new DataColumn("SumLen"));
                bedlen.Columns.Add(new DataColumn("AvgLen"));
                DataRow row = bedlen.NewRow();
                row["SumBed"] = "0";
                row["AvgBed"] = "0";
                row["SumLen"] = "0";
                row["AvgLen"] = "0";
                bedlen.Rows.Add(row);
            }

            //查询特定Pod数总和及平均特定Pod数  
            DataTable pod = new DataTable();
            try
            {
                if (SQLCommand.IsOracle)
                    sql = "select COUNT(*) as SumPod,Round(COUNT(*)/(ceil(sysdate-trunc(sysdate,'yyyy'))/14),2) as AvgPod from Pod_tb left join order_tb on pod_tb.order_id=order_tb.id and to_char(sysdate,'yyyy')=to_char(time,'yyyy') and o_PLID=" + PLID + "";
                else
                    sql = "select COUNT(*) as SumPod,Round(COUNT(*)/(CONVERT(float,datepart(dayofyear,getDate()))/14),2) as AvgPod from Pod_tb left join order_tb on pod_tb.order_id=order_tb.id and DATEPART(YYYY,GETDATE())=DATEPART(YYYY,time) and o_PLID=" + PLID + "";
                sc = new SQLCommand(sql);
                pod = sc.ExecuteScalars();
            }
            catch (Exception)
            {
                pod.Columns.Add(new DataColumn("SumPod"));
                pod.Columns.Add(new DataColumn("AvgPod"));
                DataRow row = pod.NewRow();
                row["SumPod"] = "0";
                row["AvgPod"] = "0";
                pod.Rows.Add(row);
            }

            List<DataTable> numbers = new List<DataTable>() { model, ordernumber, bedlen, pod };
            DataTable dt = new DataTable();
            string tableName = "newTable";
            for (int i = 2; i < numbers.Count; i++)
            {
                if (i == 2)
                {
                    dt = AssistFun.UniteDataTable(model, ordernumber, "newtable");
                    dt = AssistFun.UniteDataTable(dt, numbers[i], tableName);
                }
                else
                    dt = AssistFun.UniteDataTable(dt, numbers[i], tableName);
            }
            List<Number> result = AssistFun.DataTableToList<Number>(dt);
            if (result != null)
            {
                return AssistFun.toJson(new { status = 200, tip = "成功", data = new { total = 1, rows = result } });
            }
            else
            {
                return AssistFun.toJson(new { status = 201, tip = "无数据记录", data = "" });
            }
        }

        /// <summary>
        /// 计件统计 
        /// </summary>
        /// <param name="year">年份</param>
        /// <param name="model">单位类型</param>
        /// <returns></returns>
        [HttpGet, Route("PieceworkStatistics")]
        public HttpResponseMessage PieceStatistical(string sTime, string eTime, string model, int userId, int PLID)
        {
            DateTime STime = Convert.ToDateTime(sTime);
            DateTime ETime = Convert.ToDateTime(eTime);
            string token = Request.Headers.Authorization.Parameter;
            int loginId = GetCrrentIDByToken();
            SQLCommand sc = new SQLCommand("select power from User_tb where login_id=" + loginId + "");
            int power = int.Parse(sc.ExecuteScalar());
            //查询出员工姓名，计件工资总额   因为前端普通员工是也传了userId我  所以后台没必要再判断是否是管理员身份
            string sql = "";
            //if (power == 1)
            //{
                if (userId != 0)
                {
                    int uid = Convert.ToInt32(userId);
                    if (SQLCommand.IsOracle)
                    {
                        if (model == "pod")
                            sql = "select User_tb.login_id as UserId,User_tb.name as UserName,(count(*)*weight) as SumMoney,Process_tb.code as Code,Process_tb.name as CodeName from pod_tb, user_tb, Process_tb where user_tb.login_id=" + uid + " and pod_tb.user_id = user_tb.login_id and Process_tb.PLID = user_tb.PLID and user_tb.PLID = " + PLID + " and Pod_tb.time between to_date('"+sTime+ "', 'YYYY-MM-DD HH24:MI:SS') and to_date('" + eTime + "', 'YYYY-MM-DD HH24:MI:SS') and Process_tb.code = 35 group by User_tb.login_id,User_tb.name,Process_tb.code,Process_tb.name,weight order by SumMoney";
                        else
                        {
                            sql = " and Task_tb.time between to_date('" + STime + "', 'YYYY-MM-DD HH24:MI:SS') and to_date('"+eTime+"', 'YYYY-MM-DD HH24:MI:SS') and User_tb.login_id =" + uid;
                            sql = PingJieCmd(model, PLID, sql);
                        }
                    }
                    else
                    {
                        if (model == "pod")
                             sql = "select User_tb.login_id as UserId,User_tb.name as UserName,(count(*)*weight) as SumMoney,Process_tb.code as Code,Process_tb.name as CodeName from pod_tb, user_tb, Process_tb where user_tb.login_id=" + uid + " and pod_tb.user_id = user_tb.login_id and Process_tb.PLID = user_tb.PLID and user_tb.PLID = " + PLID + " and Pod_tb.time between '" + STime + "' and '" + ETime + "' and Process_tb.code = 35 group by User_tb.login_id,User_tb.name,Process_tb.code,Process_tb.name,weight order by SumMoney";
                        else
                        {
                            sql = " and Task_tb.time between '" + STime + "' and '" + ETime + "' and User_tb.login_id =" + uid;
                            sql = PingJieCmd(model, PLID, sql);
                        }
                    }
                }
                else
                {
                    if (Convert.ToBoolean(model == "all"))
                    {
                        //    //款
                        //    sql = "select User_tb.name as UserName,count(*) as SumMoney,User_tb.code as code from style_tb,model_tb,task_tb,user_tb where style_tb.style_id=model_tb.style_id and Task_tb.id=model_tb.id and User_tb.code=Task_tb.code and Task_tb.code between 10 and 29 and user_tb.PLID="+PLID+ " and to_char(Task_tb.time,'yyyy')=" + years + " and to_char(Task_tb.time,'mm')=" + yearss + " group by User_tb.name,user_tb.code order by SumMoney";
                        //    sc = new SQLCommand(sql);
                        //    DataTable first = sc.ExecuteScalars();
                        //    //生产单
                        //    sql = "select User_tb.name as UserName,count(*) as SumMoney,User_tb.code as code from Order_tb,Task_tb,User_tb where Task_tb.id=Order_tb.id and User_tb.code=Task_tb.code and Task_tb.code between 30 and 49 and user_tb.PLID=" + PLID + " and to_char(Task_tb.time,'yyyy')=" + years + " and to_char(Task_tb.time,'mm')=" + yearss + " group by User_tb.name,user_tb.code order by SumMoney";
                        //    sc = new SQLCommand(sql);
                        //    DataTable second = sc.ExecuteScalars();
                        //    //pod
                        //    sql = "select User_tb.name as UserName,count(*) as SumMoney,User_tb.code as code from pod_tb,User_tb where pod_tb.user_id=user_tb.login_id user_tb.PLID=" + PLID + " and to_char(Task_tb.time,'yyyy')=" + years + " and to_char(Task_tb.time,'mm')=" + yearss + " group by User_tb.name,user_tb.code order by SumMoney";
                        //    sc = new SQLCommand(sql);
                        //    DataTable third = sc.ExecuteScalars();
                        //    //件数
                        //    sql = "select User_tb.name as UserName,sum(order_tb.quantity) as SumMoney,User_tb.code as code from Order_tb,Task_tb,User_tb where Task_tb.id=Order_tb.id and User_tb.code=Task_tb.code and Task_tb.code between 30 and 49 and user_tb.PLID=" + PLID + " and to_char(Task_tb.time,'yyyy')=" + years + " and to_char(Task_tb.time,'mm')=" + yearss + " group by User_tb.name,user_tb.code order by SumMoney";
                        //    sql = "select User_tb.name as UserName,count(*) as SumMoney,User_tb.code as code from style_tb,model_tb,task_tb,user_tb where style_tb.style_id=model_tb.style_id and Task_tb.id=model_tb.id and User_tb.code=Task_tb.code and Task_tb.code in (11, 13, 15, 16, 18, 22, 23, 25, 26, 27) and user_tb.PLID="+PLID+ " and to_char(Task_tb.time,'yyyy')=" + years + " and to_char(Task_tb.time,'mm')=" + yearss + " group by User_tb.name,user_tb.code order by SumMoney";
                        //    sc = new SQLCommand(sql);
                        //    DataTable first = sc.ExecuteScalars();
                        //    //生产单
                        //    sql = "select User_tb.name as UserName,count(*) as SumMoney,User_tb.code as code from Order_tb,Task_tb,User_tb where Task_tb.id=Order_tb.id and User_tb.code=Task_tb.code and Task_tb.code in (33,42,43,45,46) and user_tb.PLID=" + PLID + " and to_char(Task_tb.time,'yyyy')=" + years + " and to_char(Task_tb.time,'mm')=" + yearss + " group by User_tb.name,user_tb.code order by SumMoney";
                        //    sc = new SQLCommand(sql);
                        //    DataTable second = sc.ExecuteScalars();
                        //    //pod
                        //    sql = "select User_tb.name as UserName,count(*) as SumMoney,User_tb.code as code from pod_tb,Order_tb,Task_tb,User_tb where pod_tb.order_id=order_tb.id and Task_tb.id=Order_tb.id and User_tb.code=Task_tb.code and Task_tb.code in (35) and user_tb.PLID=" + PLID + " and to_char(Task_tb.time,'yyyy')=" + years + " and to_char(Task_tb.time,'mm')=" + yearss + " group by User_tb.name,user_tb.code order by SumMoney";
                        //    sc = new SQLCommand(sql);
                        //    DataTable third = sc.ExecuteScalars();
                        //    //件数
                        //    sql = "select User_tb.name as UserName,sum(order_tb.quantity) as SumMoney,User_tb.code as code from Order_tb,Task_tb,User_tb where Task_tb.id=Order_tb.id and User_tb.code=Task_tb.code and Task_tb.code in (34)  and user_tb.PLID=" + PLID + " and to_char(Task_tb.time,'yyyy')=" + years + " and to_char(Task_tb.time,'mm')=" + yearss + " group by User_tb.name,user_tb.code order by SumMoney";
                        //    sc = new SQLCommand(sql);
                        //    DataTable forth = sc.ExecuteScalars();
                        //    //床数
                        //    sql = "select User_tb.name as UserName,sum(order_tb.bed) as SumMoney,User_tb.code as code from Order_tb,Bed_tb,Task_tb,User_tb where Order_tb.id =Bed_tb.order_id and Task_tb.id=Order_tb.id and User_tb.code=Task_tb.code and Task_tb.code in (37,48,101,107) and user_tb.PLID=" + PLID + " and to_char(Task_tb.time,'yyyy')=" + years + " and to_char(Task_tb.time,'mm')=" + yearss + " group by User_tb.name,user_tb.code order by SumMoney";
                        //    sc = new SQLCommand(sql);
                        //    DataTable fifth = sc.ExecuteScalars();
                        //    //创建一个新的DataTable来装
                        //    DataTable result = new DataTable();
                        //    List<DataTable> numbers = new List<DataTable>() { first, second, third, forth,fifth };
                        //    result = first.Clone();
                        //    result.Rows.Clear();
                        //    object[] obj = new object[result.Columns.Count];
                        //    for (int i = 2; i < numbers.Count; i++)
                        //    {
                        //        if (i == 2)
                        //        {
                        //            result = AssistFun.UniteDataTables(first,second );
                        //            result = AssistFun.UniteDataTables(result, numbers[i]);
                        //        }
                        //        else
                        //        {
                        //            result = AssistFun.UniteDataTables(result, numbers[i]);
                        //        }
                        //    }
                        //    if (result != null)
                        //    {
                        //        List<ShowPiece> pieces = new List<ShowPiece>();
                        //        List<string> name = new List<string>();
                        //        List<double> money = new List<double>();
                        //        List<int> code = new List<int>();
                        //        if (result == null)
                        //            return AssistFun.toJson(new RespondModel(201, "无数据记录"));
                        //        foreach (DataRow dr in result.Rows)
                        //        {
                        //            name.Add(Convert.ToString(dr["UserName"]));
                        //            money.Add(Convert.ToDouble(dr["SumMoney"]));
                        //            code.Add(Convert.ToInt32(dr["code"]));
                        //        }
                        //        ShowPiece piece = new ShowPiece()
                        //        {
                        //            status = 200,
                        //            data = new Data()
                        //            {
                        //                total = result.Rows.Count,
                        //                X = name,
                        //                Y = money,
                        //                code=code
                        //            }
                        //        };
                        //        return AssistFun.toJson(piece);
                        //    }
                        //    else
                        //    {
                        //        return AssistFun.toJson(new RespondModel(201, "无数据记录"));
                        //    }
                    }
                    else
                    {
                        if (SQLCommand.IsOracle)
                        {
                            if (model == "pod")
                              sql = "select User_tb.login_id as UserId,User_tb.name as UserName,(count(*)*weight) as SumMoney,Process_tb.code as Code,Process_tb.name as CodeName from pod_tb, user_tb, Process_tb where pod_tb.user_id = user_tb.login_id and Process_tb.PLID = user_tb.PLID and user_tb.PLID = " + PLID + " and Pod_tb.time between to_date('" + sTime + "', 'YYYY-MM-DD HH24:MI:SS') and to_date('" + eTime + "', 'YYYY-MM-DD HH24:MI:SS') and Process_tb.code = 35 group by User_tb.login_id,User_tb.name,Process_tb.code,Process_tb.name,weight order by SumMoney";

                            else
                            {
                                sql = " and Task_tb.time between to_date('" + sTime + "', 'YYYY-MM-DD HH24:MI:SS') and to_date('" + eTime + "', 'YYYY-MM-DD HH24:MI:SS') ";                                
                                sql = PingJieCmd(model, PLID, sql);
                            }
                        }
                        else
                        {
                            if (model == "pod")
                               sql = "select User_tb.login_id as UserId,User_tb.name as UserName,(count(*)*weight) as SumMoney,Process_tb.code as Code,Process_tb.name as CodeName from pod_tb, user_tb, Process_tb where pod_tb.user_id = user_tb.login_id and Process_tb.PLID = user_tb.PLID and user_tb.PLID = " + PLID + " and Pod_tb.time between '" + STime + "' and '" + ETime + "' and Process_tb.code = 35 group by User_tb.login_id,User_tb.name,Process_tb.code,Process_tb.name,weight order by SumMoney";
                            else
                            {                              
                                sql = " and Task_tb.time between '" + STime + "' and '" + ETime + "'";
                                sql = PingJieCmd(model, PLID, sql);
                            }
                        }
                    }
                }

            //}
            //else
            //{
            //    if (SQLCommand.IsOracle)
            //    {
            //        if (model == "pod")
            //            sql = "select User_tb.login_id as UserId,User_tb.name as UserName,(count(*)*weight) as SumMoney,Process_tb.code as Code,Process_tb.name as CodeName from pod_tb, user_tb, Process_tb where pod_tb.user_id = user_tb.login_id and Process_tb.PLID = user_tb.PLID and user_tb.PLID = " + PLID + " and Pod_tb.time between '" + STime + "' and '" + ETime + "' and Process_tb.code = 35 and  User_tb.login_id =" + loginId + " group by User_tb.login_id,User_tb.name,Process_tb.code,Process_tb.name,weight order by SumMoney";
            //        //sql = "select User_tb.login_id as UserId,User_tb.name as UserName,(count(*)*weight) as SumMoney,Task_tb.code as Code,Process_tb.name as CodeName from pod_tb,user_tb,Process_tb,Task_tb where task_tb.code=Process_tb.code and pod_tb.user_id=user_tb.login_id and task_tb.user_id=Pod_tb.user_id and Process_tb.PLID=user_tb.PLID and user_tb.PLID=" + PLID + " and Pod_tb.time between '" + STime + "' and '" + ETime + "' and task_tb.code=35 and  User_tb.login_id =" + loginId + " group by User_tb.login_id,User_tb.name,Task_tb.code,Process_tb.name,weight order by SumMoney";
            //        else
            //        {
            //            sql = sql = " and Task_tb.time between '" + STime + "' and '" + ETime + "'" + " and User_tb.login_id =" + loginId;
            //            sql = PingJieCmd(model, PLID, sql);
            //        }
            //    }
            //    else
            //    {
            //        if (model == "pod")
            //            sql = "select User_tb.login_id as UserId,User_tb.name as UserName,(count(*)*weight) as SumMoney,Process_tb.code as Code,Process_tb.name as CodeName from pod_tb, user_tb, Process_tb where pod_tb.user_id = user_tb.login_id and Process_tb.PLID = user_tb.PLID and user_tb.PLID = " + PLID + " and Pod_tb.time between '" + STime + "' and '" + ETime + "' and Process_tb.code = 35 and  User_tb.login_id =" + loginId + " group by User_tb.login_id,User_tb.name,Process_tb.code,Process_tb.name,weight order by SumMoney";
            //        //sql = "select User_tb.login_id as UserId,User_tb.name as UserName,(count(*)*weight) as SumMoney,Task_tb.code as Code,Process_tb.name as CodeName from pod_tb,user_tb,Process_tb,Task_tb where task_tb.code=Process_tb.code and pod_tb.user_id=user_tb.login_id and task_tb.user_id=Pod_tb.user_id and Process_tb.PLID=user_tb.PLID and user_tb.PLID=" + PLID + " and Pod_tb.time between '" + STime + "' and '" + ETime + "' and task_tb.code=35 and User_tb.login_id =" + loginId + " group by User_tb.login_id,User_tb.name,Task_tb.code,Process_tb.name,weight order by SumMoney";
            //        else
            //        {
            //            if (SQLCommand.IsOracle)
            //                sql = " and Task_tb.time between to_date('2018/8/1 0:00:00', 'YYYY-MM-DD HH24:MI:SS') and to_date('2018/8/3 23:59:00', 'YYYY-MM-DD HH24:MI:SS')";
            //            else
            //                sql = " and Task_tb.time between '" + STime + "' and '" + ETime + "' and User_tb.login_id =" + loginId;
            //            sql = PingJieCmd(model, PLID,sql) ;
            //        }
            //    }
            //}
            sc = new SQLCommand(sql);
            DataTable dt = sc.ExecuteScalars();
            
            if (dt != null)
            {
                List<ShowPieceModel> spms = AssistFun.DataTableToList<ShowPieceModel>(dt);
                HttpContext.Current.Session["Piece"] = dt;
                return AssistFun.toJson(new { status=200,data=spms });
            }
            else
                return AssistFun.toJson(new RespondModel(201, "无数据记录"));
        }
        

        /// <summary>
        /// 计件统计的下载
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <param name="type"></param>
        [HttpGet,Route("downpice")]
        public HttpResponseMessage PiceDownLoad(string sTime,string eTime,string model)
        {
            List<string> li2 = new List<string>(); //存放一整列所有的值 
            DataTable datas = HttpContext.Current.Session["Piece"] as DataTable;
            if (datas == null)
                return AssistFun.toJson(new { status = 201, tip = "没有数据" });
            foreach (DataRow drs in datas.Rows)
            {
                li2.Add(drs["CodeName"].ToString());
            }            
            //去重
            for (int i = 0; i < li2.Count; i++)  //外循环是循环的次数
            {
                for (int j = li2.Count - 1; j > i; j--)  //内循环是 外循环一次比较的次数
                {

                    if (li2[i] == li2[j])
                        li2.RemoveAt(j);
                }
            }
            try
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add(new DataColumn("工号"));
                newTable.Columns.Add(new DataColumn("姓名"));
                DataRow dr = newTable.NewRow();
                for (int i = 0; i < li2.Count; i++)
                {
                    newTable.Columns.Add(new DataColumn(li2[i]));
                }
                newTable.Rows.Add(dr);
                //list分组  
                List<ShowPieceModel> list = AssistFun.DataTableToList<ShowPieceModel>(datas);
                IEnumerable<IGrouping<int, ShowPieceModel>> query = list.GroupBy(pet => pet.UserId, pet => pet);
                foreach (IGrouping<int, ShowPieceModel> info in query)
                {
                    List<ShowPieceModel> pice = info.ToList();//分组后的集  也可循环得到分组后，集合中的对象，可以用info.Key去控制 
                    DataRow dr1 = newTable.NewRow();
                    dr1["工号"] = pice[0].UserId;
                    dr1["姓名"] = pice[0].UserName;
                    for (int i = 0; i < pice.Count; i++)
                    {
                        for (int j = 0; j < li2.Count; j++)
                        {
                            if (pice[i].CodeName == li2[j])
                            {
                                dr1[pice[i].CodeName] = pice[i].SumMoney;
                                break;
                            }
                        }
                    }
                    newTable.Rows.Add(dr1);
                }
                newTable.Rows.Remove(newTable.Rows[0]);
                ChangeExcelDown(newTable, model, Convert.ToDateTime(sTime), Convert.ToDateTime(eTime));
            }
            catch (Exception e)
            {
                return AssistFun.toJson(new { status = 201, tip = e.Message });
            }            
            return AssistFun.toJson(new { status = 200, tip = "成功" });
        }

        /// <summary>
        /// datatable 生成excel并且下载文件
        /// </summary>
        /// <param name="dt">datatable</param>
        /// <param name="type">下载类型</param>
        private void ChangeExcelDown(DataTable dt,string type,DateTime sTime,DateTime eTime)
        {
            string FileName = m_buyiRoot+ "\\buyiUpload\\" + type + ".xls";  //文件存放路径
            if (File.Exists(FileName)) 
                File.Delete(FileName);
            FileStream objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
            StreamWriter objStreamWriter = new StreamWriter(objFileStream, Encoding.Unicode);
            string strLine ="     "+ type + "("+Convert.ToDateTime(sTime).ToString("yyyy/MM/dd")+" - "+ Convert.ToDateTime(eTime).ToString("yyyy/MM/dd")+")";
            objStreamWriter.WriteLine(strLine);
            strLine = "";            
            for (int i = 0; i < dt.Columns.Count; i++)
                strLine = strLine + dt.Columns[i].Caption.ToString() + Convert.ToChar(9);      //写列标题
            objStreamWriter.WriteLine(strLine);
            strLine = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i].ItemArray[j] == null)
                        strLine = strLine + " " + Convert.ToChar(9);                                    //写内容
                    else
                    {
                        string rowstr = "";
                        rowstr = dt.Rows[i].ItemArray[j].ToString();
                        if (rowstr.IndexOf("\r\n") > 0)
                            rowstr = rowstr.Replace("\r\n", " ");
                        if (rowstr.IndexOf("\t") > 0)
                            rowstr = rowstr.Replace("\t", " ");
                        strLine = strLine + rowstr + Convert.ToChar(9);
                    }
                }
                objStreamWriter.WriteLine(strLine);
                strLine = "";
            }
            objStreamWriter.Close();
            objFileStream.Close();
            Download(FileName);
            File.Delete(FileName);
        }

        /// <summary>
        /// 样板统计饼图  
        /// </summary>  
        /// <returns></returns>
        [HttpGet, Route("TemplateStatistics")]
        public HttpResponseMessage TemplateStatistics()
        {
            string sql = "";
            SQLCommand sc = null;
            string SumNumber = null;
            if (SQLCommand.IsOracle)
            {
                sql = "select COUNT(*) from Order_tb where creater_time between add_months(sysdate,-12)+1 and sysdate";
                sc = new SQLCommand(sql);
                SumNumber = sc.ExecuteScalar();
                sql = "select value,name from (select COUNT(*) as value,Model_tb.name as name from model_tb left join order_tb ON model_tb.id=order_tb.o_model_id where Order_tb.creater_time between add_months(sysdate,-12)+1 and sysdate GROUP BY model_tb.name) where rownum<=10";
                sc = new SQLCommand(sql);
            }
            else
            {
                sql = "SELECT COUNT(*) number FROM Order_tb WHERE  creater_time BETWEEN dateadd(DAY,-365,GETDATE()) and GETDATE()";
                sc = new SQLCommand(sql);
                SumNumber = sc.ExecuteScalar();
                sql = "SELECT TOP 10 COUNT(*) as value,Model_tb.name as name FROM model_tb left join order_tb ON model_tb.id=order_tb.o_model_id where Order_tb.creater_time between dateadd(DAY,-365,GETDATE()) and GETDATE() GROUP BY model_tb.name";
                sc = new SQLCommand(sql);
            }
            DataTable dt = sc.ExecuteScalars();
            int other = 0;
            if (dt != null)
            {
                if (SQLCommand.IsOracle)
                {
                    List<OrcPieModel> piedatas = AssistFun.DataTableToList<OrcPieModel>(dt);
                    for (int i = 0; i < piedatas.Count; i++)
                    {
                        other += Convert.ToInt32(piedatas[i].value);
                    }
                    piedatas.Add(new OrcPieModel() { value = Convert.ToDecimal(SumNumber) - other, name = "其他" });
                    return AssistFun.toJson(new RespondModel(200, "成功", new data(piedatas.Count, piedatas.ConvertAll(c => (object)c))));
                }
                else
                {
                    List<PieModel> piedata = AssistFun.DataTableToList<PieModel>(dt);
                    for (int i = 0; i < piedata.Count; i++)
                    {
                        other += piedata[i].value;
                    }
                    piedata.Add(new PieModel() { value = Convert.ToInt32(SumNumber) - other, name = "其他" });
                    return AssistFun.toJson(new RespondModel(200, "成功", new data(piedata.Count, piedata.ConvertAll(c => (object)c))));
                }
            }
            else
            {
                return AssistFun.toJson(new RespondModel(201, "无数据记录"));
            }
        }
        #region 拼接命令  
        /// <summary>
        /// 拼接命令  件数  30-49 是否要排除pod
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        private string PingJieCmd(string model, int PLID, string sql)
        {
            string sqlAdd = "";
            if (Convert.ToBoolean(model == "款"))
            {
                sqlAdd = "select User_tb.login_id as UserId,User_tb.name as UserName,(count(*)*weight) as SumMoney,task_tb.code as Code,Process_tb.name as CodeName from model_tb,task_tb,user_tb,Process_tb where Task_tb.code = Process_tb.code and Task_tb.id = model_tb.id and User_tb.login_id = Task_tb.user_id and Task_tb.code between 10 and 29 and process_tb.PLId = user_tb.PLID and user_tb.PLID =" + PLID;
                sqlAdd = sqlAdd + sql + " group by User_tb.login_id ,User_tb.name,task_tb.code,Process_tb.name,weight order by SumMoney ";
            }
            else if (Convert.ToBoolean(model == "生产单"))
            {
                sqlAdd = "select User_tb.name as UserName,(count(*)*weight) as SumMoney ,User_tb.login_id as UserId,Task_tb.code as Code,Process_tb.name as CodeName from Order_tb, Task_tb, User_tb, process_tb where Task_tb.id = Order_tb.id and User_tb.login_id = Task_tb.user_id and Task_tb.code = Process_tb.code and process_tb.plid = user_tb.PLID and Task_tb.code between 30 and 49 and task_tb.code!=35  and user_tb.PLID = " + PLID;
                sqlAdd = sqlAdd + sql + " group by User_tb.name,user_tb.login_id,Task_tb.code,Process_tb.name,weight order by SumMoney ";
            }
            else if (Convert.ToBoolean(model == "件数"))
            {
                sqlAdd = "select User_tb.login_id as UserId,User_tb.name as UserName,Order_tb.quantity*Process_tb.weight as SumMoney,Task_tb.code as Code,Process_tb.name as CodeName from Order_tb left join Task_tb on Order_tb.id = Task_tb.id  left join User_tb on Task_tb.user_id = User_tb.login_id left join Process_tb on Task_tb.code = Process_tb.code and User_tb.PLID = Process_tb.plid where Task_tb.code between 30 and 49  and User_tb.PLID = " + PLID;
                sqlAdd = sqlAdd + sql;
                if(SQLCommand.IsOracle) //oracle 给表取别名不加as 或者不给别名  包括子查询
                    sqlAdd = " select UserId,UserName,Code,CodeName,Sum(SumMoney) as SumMoney  from (" + sqlAdd + ") f group by UserId,UserName,Code,CodeName";
                else
                    sqlAdd = " select UserId,UserName,Code,CodeName,Sum(SumMoney) as SumMoney  from (" + sqlAdd + ") as f group by UserId,UserName,Code,CodeName";
            }
            //else if (Convert.ToBoolean(model == "长度"))  
            //{
            //    sqlAdd = ""; 
            //}
            else if (Convert.ToBoolean(model == "床"))
            {
                sqlAdd = "select User_tb.login_id as UserId, User_tb.name as UserName,order_tb.bed*weight as SumMoney,Task_tb.code as Code ,Process_tb.name as CodeName from Order_tb, Bed_tb, Task_tb, User_tb, Process_tb where Order_tb.id = Bed_tb.order_id and Task_tb.id = Order_tb.id and User_tb.login_id = Task_tb.user_id and Process_tb.PLID = user_tb.PLID and process_tb.code = Task_tb.code and Task_tb.code in (37, 48, 101, 107) and user_tb.PLID = " + PLID;
                sqlAdd = sqlAdd + sql + " group by User_tb.login_id,User_tb.name,Task_tb.code,Process_tb.name,order_tb.bed,weight order by SumMoney desc";
            }
            else
            {
                sqlAdd = "";
            }
            return sqlAdd;
        }
        #endregion


        /// <summary>
        /// 款式判断
        /// </summary>
        /// <param name="name"></param>
        /// <param name="modelcount"></param>
        /// <param name="ordercount"></param>
        /// <param name="numbercount"></param>
        /// <param name="bedcount"></param>
        /// <param name="lencount"></param>
        /// <param name="podcount"></param>
        /// <returns></returns>
        private ShowCartogram Choose(List<double> modelcount, List<double> ordercount, List<double> numbercount, List<double> bedcount, List<double> lencount, List<double> podcount)
        {
            var array = new int[] { modelcount.Count, ordercount.Count, numbercount.Count, bedcount.Count, lencount.Count, podcount.Count };
            int max = array.Max();
            List<string> name = new List<string> { "样板数", "生产单数", "件数", "床数", "铺布总长度", "特定Pod数" };
            List<LegendInfo> legends = new List<LegendInfo>();
            for (int i = 0; i < name.Count; i++)
            {
                LegendInfo legendinfo = new LegendInfo
                {
                    CompanyName = name[i]
                };
                switch (name[i])
                {
                    case "样板数":
                        legendinfo.Data = modelcount;
                        break;
                    case "生产单数":
                        legendinfo.Data = ordercount;
                        break;
                    case "件数":
                        legendinfo.Data = numbercount;
                        break;
                    case "床数":
                        legendinfo.Data = bedcount;
                        break;
                    case "铺布总长度":
                        legendinfo.Data = lencount;
                        break;
                    case "特定Pod数":
                        legendinfo.Data = podcount;
                        break;
                    default:
                        break;
                }

                legends.Add(legendinfo);
            }
            ShowCartogram cartogram = new ShowCartogram()
            {
                status = 200,
                data = new DataInfo()
                {
                    total = max,
                    legend = name,
                    series = legends
                }
            };
            return cartogram;
        }

        /// <summary>
        /// 本年统计图语句拼接
        /// </summary> 
        /// <returns></returns>
        private string Model()
        {
            string sqlpingjie = "";
            string sqlone = "   UNION SELECT '25' AS newTime,'0' AS model UNION SELECT '24' AS newTime,'0' AS model UNION SELECT '23' AS newTime,'0' AS model UNION SELECT '22' AS newTime,'0' AS model UNION SELECT '21' AS newTime,'0' AS model UNION SELECT '20' AS newTime,'0' AS model UNION SELECT '19' AS newTime,'0' AS model UNION SELECT '18' AS newTime,'0' AS model UNION SELECT '17' AS newTime,'0' AS model";
            string sqltwo = "  UNION SELECT '16' AS newTime,'0' AS model UNION SELECT '15' AS newTime,'0' AS model UNION SELECT '14' AS newTime,'0' AS model UNION SELECT '13' AS newTime,'0' AS model UNION SELECT '12' AS newTime,'0' AS model UNION SELECT '11' AS newTime,'0' AS model UNION SELECT '10' AS newTime,'0' AS model UNION SELECT '9' AS newTime,'0' AS model UNION SELECT '8' AS newTime,'0' AS model UNION SELECT '7' AS newTime,'0' AS model";
            string sqlthree = "  UNION SELECT '6' AS newTime,'0' AS model UNION SELECT '5' AS newTime,'0' AS model UNION SELECT '4' AS newTime,'0' AS model UNION SELECT '3' AS newTime,'0' AS model UNION SELECT '2' AS newTime,'0' AS model UNION SELECT '1' AS newTime,'0' AS model UNION SELECT '0' AS newTime,'0' AS model) A GROUP BY A.newTime ORDER BY A.newTime DESC";
            sqlpingjie = sqlone + sqltwo + sqlthree;
            return sqlpingjie;
        }
        private string OrderNumber()
        {
            string sqlpingjie = "";
            string sqlone = "   UNION SELECT '25' AS newTime,'0' AS orders,'0' AS number UNION SELECT '24' AS newTime,'0' AS orders,'0' AS number UNION SELECT '23' AS newTime,'0' AS orders,'0' AS number UNION SELECT '22' AS newTime,'0' AS orders,'0' AS number UNION SELECT '21' AS newTime,'0' AS orders,'0' AS number UNION SELECT '20' AS newTime,'0' AS orders,'0' AS number UNION SELECT '19' AS newTime,'0' AS orders,'0' AS number UNION SELECT '18' AS newTime,'0' AS orders,'0' AS number UNION SELECT '17' AS newTime,'0' AS orders,'0' AS number";
            string sqltwo = "  UNION SELECT '16' AS newTime,'0' AS orders,'0' AS number UNION SELECT '15' AS newTime,'0' AS orders,'0' AS number UNION SELECT '14' AS newTime,'0' AS orders,'0' AS number UNION SELECT '13' AS newTime,'0' AS orders,'0' AS number UNION SELECT '12' AS newTime,'0' AS orders,'0' AS number UNION SELECT '11' AS newTime,'0' AS orders,'0' AS number UNION SELECT '10' AS newTime,'0' AS orders,'0' AS number UNION SELECT '9' AS newTime,'0' AS orders,'0' AS number UNION SELECT '8' AS newTime,'0' AS orders,'0' AS number UNION SELECT '7' AS newTime,'0' AS orders,'0' AS number";
            string sqlthree = "  UNION SELECT '6' AS newTime,'0' AS orders,'0' AS number UNION SELECT '5' AS newTime,'0' AS orders,'0' AS number UNION SELECT '4' AS newTime,'0' AS orders,'0' AS number UNION SELECT '3' AS newTime,'0' AS orders,'0' AS number UNION SELECT '2' AS newTime,'0' AS orders,'0' AS number UNION SELECT '1' AS newTime,'0' AS orders,'0' AS number UNION SELECT '0' AS newTime,'0' AS orders,'0' AS number) A GROUP BY A.newTime ORDER BY A.newTime DESC";
            sqlpingjie = sqlone + sqltwo + sqlthree;
            return sqlpingjie;
        }
        private string BedLen()
        {
            string sqlpingjie = "";
            string sqlone = "   UNION SELECT '25' AS newTime,'0' AS bed,'0' AS len UNION SELECT '24' AS newTime,'0' AS bed,'0' AS len UNION SELECT '23' AS newTime,'0' AS bed,'0' AS len UNION SELECT '22' AS newTime,'0' AS bed,'0' AS len UNION SELECT '21' AS newTime,'0' AS bed,'0' AS len UNION SELECT '20' AS newTime,'0' AS bed,'0' AS len UNION SELECT '19' AS newTime,'0' AS bed,'0' AS len UNION SELECT '18' AS newTime,'0' AS bed,'0' AS len UNION SELECT '17' AS newTime,'0' AS bed,'0' AS len";
            string sqltwo = "  UNION SELECT '16' AS newTime,'0' AS bed,'0' AS len UNION SELECT '15' AS newTime,'0' AS bed,'0' AS len UNION SELECT '14' AS newTime,'0' AS bed,'0' AS len UNION SELECT '13' AS newTime,'0' AS bed,'0' AS len UNION SELECT '12' AS newTime,'0' AS bed,'0' AS len UNION SELECT '11' AS newTime,'0' AS bed,'0' AS len UNION SELECT '10' AS newTime,'0' AS bed,'0' AS len UNION SELECT '9' AS newTime,'0' AS bed,'0' AS len UNION SELECT '8' AS newTime,'0' AS bed,'0' AS len UNION SELECT '7' AS newTime,'0' AS bed,'0' AS len";
            string sqlthree = "  UNION SELECT '6' AS newTime,'0' AS bed,'0' AS len UNION SELECT '5' AS newTime,'0' AS bed,'0' AS len UNION SELECT '4' AS newTime,'0' AS bed,'0' AS len UNION SELECT '3' AS newTime,'0' AS bed,'0' AS len UNION SELECT '2' AS newTime,'0' AS bed,'0' AS len UNION SELECT '1' AS newTime,'0' AS bed,'0' AS len UNION SELECT '0' AS newTime,'0' AS bed,'0' AS len) A GROUP BY A.newTime ORDER BY A.newTime DESC";
            sqlpingjie = sqlone + sqltwo + sqlthree;
            return sqlpingjie;
        }
        private string Pod()
        {
            string sqlpingjie = "";
            string sqlone = "  UNION SELECT '25' AS newTime,'0' AS pod UNION SELECT '24' AS newTime,'0' AS pod UNION SELECT '23' AS newTime,'0' AS pod UNION SELECT '22' AS newTime,'0' AS pod UNION SELECT '21' AS newTime,'0' AS pod UNION SELECT '20' AS newTime,'0' AS pod UNION SELECT '19' AS newTime,'0' AS pod UNION SELECT '18' AS newTime,'0' AS pod UNION SELECT '17' AS newTime,'0' AS pod";
            string sqltwo = "  UNION SELECT '16' AS newTime,'0' AS pod UNION SELECT '15' AS newTime,'0' AS pod UNION SELECT '14' AS newTime,'0' AS pod UNION SELECT '13' AS newTime,'0' AS pod UNION SELECT '12' AS newTime,'0' AS pod UNION SELECT '11' AS newTime,'0' AS pod UNION SELECT '10' AS newTime,'0' AS pod UNION SELECT '9' AS newTime,'0' AS pod UNION SELECT '8' AS newTime,'0' AS pod UNION SELECT '7' AS newTime,'0' AS pod";
            string sqlthree = "  UNION SELECT '6' AS newTime,'0' AS pod UNION SELECT '5' AS newTime,'0' AS pod UNION SELECT '4' AS newTime,'0' AS pod UNION SELECT '3' AS newTime,'0' AS pod UNION SELECT '2' AS newTime,'0' AS pod UNION SELECT '1' AS newTime,'0' AS pod UNION SELECT '0' AS newTime,'0' AS pod ) A GROUP BY A.newTime ORDER BY A.newTime DESC";
            sqlpingjie = sqlone + sqltwo + sqlthree;
            return sqlpingjie;
        }
    }
}
